package com.xian.gaoxiao.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.xian.gaoxiao.db.CommentTable.CommentColumns;
import com.xian.gaoxiao.db.TaskItemTable.TaskItemColumns;
import com.xian.gaoxiao.model.TaskComment;
import com.xian.gaoxiao.model.TaskItem;
import com.xian.gaoxiao.utils.Utilities;

public class TaskDBControl {

	public static final String WHERE_COMMENT_BY_TASKID = CommentColumns.TASKID
			+ " = ?";
	public static final String WHERE_COMMENT_BY_ID = CommentColumns.ID + " = ?";

	public static final String ORDER_COMMENT_SORT_CREATEDATE = CommentColumns.SORT
			+ " DESC , " + CommentColumns.CREATEDATE + " DESC ";
	
	public static final String WHERE_TASKITEM_BY_ID = TaskItemColumns.ID + " = ?";
	public static final String ORDER_TASKITEM_SORT_CREATEDATE = TaskItemColumns.SORT
			+ " DESC , " + TaskItemColumns.CREATEDATE + " DESC ";
	
	public static final String WHERE_TASKITEM_BY_RANK = TaskItemColumns.RANK + " = ?";

	private CommentTable commentTabel;
	private TaskItemTable taskItemTabel;

	private Context mContext;

	public static TaskDBControl mTaskDBControl;
	
	public static TaskDBControl getInstance(Context context){
		if(mTaskDBControl!=null){
			return mTaskDBControl;
		}
		mTaskDBControl=new TaskDBControl(context);
		return mTaskDBControl;
	}
	
	private TaskDBControl(Context context) {
		mContext = context;
		AppDatabaseHelper dbHelper = new AppDatabaseHelper(mContext);
		SQLiteDatabase mDB = dbHelper.getReadableDatabase();
		commentTabel = new CommentTable(mDB);
		taskItemTabel = new TaskItemTable(mDB);
	}

	public int insertTask(TaskItem item) {
		int result = 0;
		if (item != null) {
			result = taskItemTabel.insert(item.getAlertDate(),
					item.getEnableAlert(), item.getBeginDate(),
					item.getFinishDate(), item.getTitle(), item.getRank(),
					item.getData(), item.getPic(), item.getSound());
		}
		return result;
	}

	public int updateTask(TaskItem item) {
		int result = 0;
		if (item != null) {
			ContentValues values = new ContentValues();

			values.put(TaskItemColumns.ALERTDATE, item.getAlertDate());
			values.put(TaskItemColumns.ENABLEALERT, item.getEnableAlert());
			values.put(TaskItemColumns.BEGINDATE, item.getBeginDate());
			values.put(TaskItemColumns.FINISHDATE, item.getFinishDate());
			values.put(TaskItemColumns.STATUS, item.getStatus());
			values.put(TaskItemColumns.TITLE, item.getTitle());
			values.put(TaskItemColumns.RANK, item.getRank());
			values.put(TaskItemColumns.SORT, item.getSort());
			values.put(TaskItemColumns.DATA, item.getData());
			values.put(TaskItemColumns.PIC, item.getPic());
			values.put(TaskItemColumns.SOUND, item.getSound());

			result = taskItemTabel.update(values, TaskItemColumns.ID + " = "
					+ item.getId(), null);
		}
		return result;
	}

	public int deleteTask(Integer id) {
		int result = 0;
		if (id != null) {
			result = taskItemTabel.delete(id);
			deleteCommentByTaskID(id);

		}
		return result;
	}

	public TaskItem getTask(Integer id) {
		TaskItem item = null;
		Cursor cursor = null;
		try {
			cursor = taskItemTabel.query(null, WHERE_TASKITEM_BY_ID, new String[]{id.toString()}, null, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {

					List<TaskComment> commentList = queryComment(cursor
							.getInt(cursor.getColumnIndex(TaskItemColumns.ID)));

					item = new TaskItem(
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.ID)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.CREATEDATE)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.ALERTDATE)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.ENABLEALERT)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.BEGINDATE)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.FINISHDATE)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.MODIFYDATE)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.STATUS)),
							cursor.getString(cursor
									.getColumnIndex(TaskItemColumns.TITLE)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.RANK)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.SORT)),
							cursor.getString(cursor
									.getColumnIndex(TaskItemColumns.DATA)),
							cursor.getBlob(cursor
									.getColumnIndex(CommentColumns.PIC)),
							cursor.getString(cursor
									.getColumnIndex(TaskItemColumns.DATA)),

							commentList);

					Utilities.print(item.toString());
				}
			}
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return item;

	}

	public List<TaskItem> queryTask(String selection, String[] selectionArgs ) {

		List<TaskItem> list = new ArrayList<TaskItem>();
		Cursor cursor = null;
		try {
			cursor = taskItemTabel.query(null,selection,selectionArgs,ORDER_TASKITEM_SORT_CREATEDATE,null);
			if (cursor != null) {
				while (cursor.moveToNext()) {

					TaskItem item = new TaskItem(
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.ID)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.CREATEDATE)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.ALERTDATE)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.ENABLEALERT)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.BEGINDATE)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.FINISHDATE)),
							cursor.getLong(cursor
									.getColumnIndex(TaskItemColumns.MODIFYDATE)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.STATUS)),
							cursor.getString(cursor
									.getColumnIndex(TaskItemColumns.TITLE)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.RANK)),
							cursor.getInt(cursor
									.getColumnIndex(TaskItemColumns.SORT)),
							cursor.getString(cursor
									.getColumnIndex(TaskItemColumns.DATA)),
							cursor.getBlob(cursor
									.getColumnIndex(CommentColumns.PIC)),
							cursor.getString(cursor
									.getColumnIndex(TaskItemColumns.SOUND)),
							null);

					Utilities.print(item.toString());
					list.add(item);

				}
			}
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return list;

	}

	public int insertComment(TaskComment item) {
		int result = 0;
		if (item != null) {
			result = commentTabel.insert(item.getTaskId(), item.getComment(),
					item.getPic());
		}
		return result;
	}

	public int updateComment(TaskComment item) {
		int result = 0;
		if (item != null) {
			ContentValues values = new ContentValues();

			values.put(CommentColumns.ISSHOW, item.getIsShow());
			values.put(CommentColumns.COMMENT, item.getComment());
			values.put(CommentColumns.SORT, item.getSort());
			values.put(CommentColumns.PIC, item.getPic());

			result = commentTabel.update(values, CommentColumns.ID + " = "
					+ item.getId(), null);
		}
		return result;
	}

	public int deleteCommentByID(Integer id) {
		int result = 0;
		if (id != null) {
			result = commentTabel.delete(WHERE_COMMENT_BY_ID,
					new String[] { id.toString() });
		}
		return result;
	}

	public int deleteCommentByTaskID(Integer id) {
		int result = 0;
		if (id != null) {
			result = commentTabel.delete(WHERE_COMMENT_BY_TASKID,
					new String[] { id.toString() });
		}
		return result;
	}

	public List<TaskComment> queryComment(Integer taskid) {
		List<TaskComment> list = new ArrayList<TaskComment>();
		Cursor cursor = null;
		if (taskid != null) {
			try {
				cursor = commentTabel.query(null, WHERE_COMMENT_BY_TASKID,
						new String[] { taskid.toString() },
						ORDER_COMMENT_SORT_CREATEDATE, null);

				if (cursor != null) {
					while (cursor.moveToNext()) {
						TaskComment comment = new TaskComment(
								cursor.getInt(cursor
										.getColumnIndex(CommentColumns.ID)),
								taskid,
								cursor.getLong(cursor
										.getColumnIndex(CommentColumns.CREATEDATE)),
								cursor.getLong(cursor
										.getColumnIndex(CommentColumns.MODIFYDATE)),
								cursor.getInt(cursor
										.getColumnIndex(CommentColumns.ISSHOW)),
								cursor.getString(cursor
										.getColumnIndex(CommentColumns.COMMENT)),
								cursor.getInt(cursor
										.getColumnIndex(CommentColumns.SORT)),
								cursor.getBlob(cursor
										.getColumnIndex(CommentColumns.PIC)));
						Utilities.print(comment.toString());
						list.add(comment);

					}
				}
			} finally {
				if (cursor != null) {
					cursor.close();
				}
			}
		}
		return list;

	}

	public TaskComment getTaskComment(Integer id) {
		TaskComment comment = null;
		Cursor cursor = null;
		if (id != null) {
			try {
				cursor = commentTabel.query(null, WHERE_COMMENT_BY_ID,
						new String[] { id.toString() }, null, null);
				if (cursor.moveToFirst()) {
					comment = new TaskComment(
							id,
							cursor.getInt(cursor
									.getColumnIndex(CommentColumns.TASKID)),
							cursor.getLong(cursor
									.getColumnIndex(CommentColumns.CREATEDATE)),
							cursor.getLong(cursor
									.getColumnIndex(CommentColumns.MODIFYDATE)),
							cursor.getInt(cursor
									.getColumnIndex(CommentColumns.ISSHOW)),
							cursor.getString(cursor
									.getColumnIndex(CommentColumns.COMMENT)),
							cursor.getInt(cursor
									.getColumnIndex(CommentColumns.SORT)),
							cursor.getBlob(cursor
									.getColumnIndex(CommentColumns.PIC)));
					Utilities.print(comment.toString());

				}
			} finally {
				if (cursor != null) {
					cursor.close();
				}
			}
		}
		return comment;
	}

}
